package com.ifcar.app.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class NewsSQLiteOpenHelper extends SQLiteOpenHelper{

	private static final String TAG = "newsDB";

	private static final String DATABASE_NAME = "netease_news.db";
    
    private static final int DATABASE_VERSION = 77;


	public static final String DEFAULT_SORT_ORDER = "id DESC";
	
	// 栏目
	public static final String TOP_COLUMN_TABLE = "top_columns";
	public static final String MORE_COLUMN_TABLE = "more_columns";	
	public static final String MYID = "myid";
	public static final String TNAME = "tname";
	public static final String TID = "tid";
	public static final String CID = "cid";
	
	// 投票
	public static final String VOTE_TABLE = "vote_table";
	public static final String VOTE_AUTO_ID = "auto_id"; // 自增主键
	public static final String VOTE_ID = "vote_id"; // 投票ID

	// 收藏
	public static final String COLLECT_TABLE = "collect_table";
	public static final String COLLECT_AUTOID = "auto_id"; // 自增主键
	public static final String COLLECT_PATH = "collect_path"; // 路径
	public static final String COLLECT_IMAGE_LOCAL = "image_loacl"; // 路径
	public static final String COLLECT_OBJID = "collect_id" ; // ID
	public static final String COLLECT_TITLE = "collect_title"; // 标题
	public static final String COLLECT_TYPE = "collect_type"; // 收藏类型
//	public static final String COLLECT_EXTEND = "collect_extend"; // 扩展字段  图集为图片个数 文章 待定
	public static final String COLLECT_CREATE_AT = "create_at"; // 收藏时间
	public static final String COLLECT_ISCHECKED = "ischecked"; // 收藏时间
	
	// 新闻和话题列表
	public static final String NEWS_LIST_TABLE = "news_list";
	public static final String NEWS_AUTO_ID = "news_auto_id";
	public static final String NEWS_COL_ID = "news_col_id";
	public static final String NEWS_DOC_ID = "news_doc_id";	//栏目ID或者专题ID[来自于栏目 还是来自于专题]
	public static final String NEWS_IMG_SRC = "news_img_src";
	public static final String NEWS_DIGEST = "news_digest";
	public static final String NEWS_REPLY_COUNT = "news_reply_count";	
	public static final String NEWS_URL = "news_url";
	public static final String NEWS_TITLE = "news_title";
	public static final String NEWS_PTIME = "news_ptime";
	public static final String NEWS_SUBTITLE = "news_subtitle";
	public static final String NEWS_IS_FIRST = "news_is_first";
	public static final String NEWS_IS_READ = "news_is_read";
	public static final String NEWS_IS_HASIMG = "news_is_hasimg";
	
	public static final String NEWS_SPECIAL_ID = "news_special_id";//专题ID用户普通的新闻列表，表示此新闻是带有专题
	public static final String NEWS_SPECIAL_INDEX = "news_index";
	public static final String NEWS_SPECIAL_TNAME = "news_special_tname";
	
	public static final String NEWS_EXTENDS_JSON = "news_extends_json"; //新闻扩展域
	
	//跟帖列表
	public static final String COMMENTS_LIST_TABLE = "comments_list";
	public static final String COMMENTS_AUTO_ID = "comments_auto_id";
	public static final String COMMENTS_COL_ID = "comments_col_id";	
	public static final String COMMENTS_DOC_ID = "comments_doc_id";
	public static final String COMMENTS_REPLY_COUNT = "comments_reply_count";	
	public static final String COMMENTS_TITLE = "comments_title";
	public static final String COMMENTS_TIE = "comments_tie";
	public static final String COMMENTS_AUTHOR = "comments_author";
	public static final String COMMENTS_REPLY_BOARD = "comments_reply_board";
	public static final String COMMENTS_POSTID = "comments_postid";
	public static final String COMMENTS_FLOOR = "comments_floor";
	public static final String COMMENTS_IS_READ = "comments_is_read";
	public static final String COMMENTS_PTIME = "comments_ptime";
	
	//图集列表-为奥运增加 PHOTO_COLUMN 字段
	public static final String PHOTO_LIST_TABLE = "photo_list";
	public static final String PHOTO_COLUMN= "photo_column";
	public static final String PHOTO_AUTO_ID = "photo_auto_id";
	public static final String PHOTO_SETID = "photo_setid";
	public static final String PHOTO_CLIENTCOVER = "photo_clientcover";
	public static final String PHOTO_SETNAME= "photo_setname";
	public static final String PHOTO_IMGSUM = "photo_imgsum";
	public static final String PHOTO_DATE = "photo_date";
	
	//图片列表
	public static final String PIC_TABLE = "pic";
	public static final String PIC_AUTO_ID = "pic_auto_id";
	public static final String PIC_SETID = "pic_setid";
	public static final String PIC_CHANNEL = "pic_channel";
	public static final String PIC_JSON = "pic_json";
	public static final String PIC_DATE = "pic_date";
	
	//已读状态列表
	public static final String ARTICLE_READ_LIST_TABLE = "article_read_status_list";
	public static final String ARTICLE_READ_AUTO_ID = "article_read_auto_id";
	public static final String ARTICLE_READ_COL_ID = "article_read_col_id";
	public static final String ARTICLE_READ_DOC_ID = "article_read_doc_id";
	public static final String ARTICLE_READ_STATUS = "article_read_is_read";
	public static final String ARTICLE_READ_DATE = "article_read_date";
	
	//微博OAuth token
	public static final String OAUTH_TABLE = "oauth_list";
	public static final String OAUTH_AUTO_ID = "oauth_auto_id";
	public static final String OAUTH_TYPE = "oauth_type";
	public static final String OAUTH_TOKEN = "oauth_token";
	public static final String OAUTH_TOKEN_SECRET = "oauth_token_secret";
	public static final String OAUTH_NAME = "oauth_name";
	
	//城市列表
    public static final String CITY_TABLE = "city_list";
    public static final String CITY_AUTO_ID = "city_auto_id";
    public static final String CITY_NAME = "city_name";
    public static final String CITY_PROVINCE = "city_province";
    public static final String CITY_PINYIN = "city_pinyin";
    public static final String CITY_SELECT_COUNT = "city_select_count";
    
    //视频新闻列表
    public static final String VIDEO_LIST_TABLE = "video_list";
    public static final String VIDEO_AUTO_ID = "video_auto_id";
    public static final String VIDEO_COL_ID = "video_col_id";
    public static final String VIDEO_REPLY_COUNT = "video_reply_count";
    public static final String VIDEO_DIGEST = "video_digest";
    public static final String VIDEO_DIGEST_ALL = "video_digest_all";
    public static final String VIDEO_TITLE = "video_title";
    public static final String VIDEO_HITS = "video_hits";
    public static final String VIDEO_COVER = "video_cover";
    public static final String VIDEO_REPLY_BOARD = "video_reply_board";
    public static final String VIDEO_REPLY_ID = "video_reply_id";
    public static final String VIDEO_MP4_URL = "video_mp4_url";
    public static final String VIDEO_LENGTH = "video_length";
    public static final String VIDEO_VURL = "video_vurl";
    public static final String VIDEO_VID = "video_vid";
    public static final String VIDEO_PTIME = "video_ptime";
    public static final String VIDEO_EXTENDS_JSON = "video_extends_json"; //视频扩展域
    
	
    public NewsSQLiteOpenHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
        db.execSQL("CREATE TABLE " + TOP_COLUMN_TABLE + " ("
                + MYID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + TNAME + " NTEXT,"
                + TID + " NTEXT,"
                + CID + " NTEXT"
                + ");");
        db.execSQL("CREATE TABLE " + MORE_COLUMN_TABLE + " ("
                + MYID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + TNAME + " NTEXT,"
                + TID + " NTEXT,"
                + CID + " NTEXT"
                + ");");
        
        // 投票表
        db.execSQL("CREATE TABLE " + VOTE_TABLE + " ("
                + VOTE_AUTO_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + VOTE_ID + " NTEXT"
                + ");");
        
        // 收藏表
        db.execSQL("CREATE TABLE " + COLLECT_TABLE + " ("
                + COLLECT_AUTOID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COLLECT_PATH + " NTEXT,"
                + COLLECT_OBJID + " NTEXT,"
                + COLLECT_TITLE + " NTEXT,"
                + COLLECT_TYPE + " NTEXT,"
//                + COLLECT_EXTEND + " NTEXT,"
                + COLLECT_CREATE_AT + " NTEXT,"
                + COLLECT_ISCHECKED + " NTEXT"
                + ");");
        
        // 新闻列表
        db.execSQL("CREATE TABLE " + NEWS_LIST_TABLE + " ("
                + NEWS_AUTO_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + NEWS_COL_ID + " NTEXT,"
                + NEWS_DOC_ID + " NTEXT,"
                + NEWS_IMG_SRC + " NTEXT,"
                + NEWS_DIGEST + " NTEXT,"
                + NEWS_REPLY_COUNT + " NTEXT,"
                + NEWS_URL + " NTEXT,"
                + NEWS_TITLE + " NTEXT,"
                + NEWS_PTIME + " NTEXT,"
                + NEWS_SUBTITLE + " NTEXT,"
                + NEWS_IS_FIRST + " NTEXT,"
                + NEWS_IS_READ + " NTEXT,"
                + NEWS_IS_HASIMG +" NTEXT,"
                + NEWS_SPECIAL_ID +" NTEXT,"
                + NEWS_SPECIAL_INDEX+" NTEXT,"
                + NEWS_SPECIAL_TNAME+" NTEXT,"
                + NEWS_EXTENDS_JSON+" NTEXT"
                + ");");
        
        // 跟帖列表
        db.execSQL("CREATE TABLE " + COMMENTS_LIST_TABLE + " ("
                + COMMENTS_AUTO_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COMMENTS_COL_ID + " NTEXT,"
                + COMMENTS_DOC_ID + " NTEXT,"
                + COMMENTS_REPLY_COUNT + " NTEXT,"
                + COMMENTS_TITLE + " NTEXT,"
                + COMMENTS_TIE + " NTEXT,"
                + COMMENTS_AUTHOR + " NTEXT,"
                + COMMENTS_REPLY_BOARD + " NTEXT,"
                + COMMENTS_POSTID + " NTEXT,"
                + COMMENTS_FLOOR + " NTEXT,"
                + COMMENTS_IS_READ + " NTEXT,"
                + COMMENTS_PTIME + " NTEXT"
                + ");");
        
        // 图集列表
        db.execSQL("CREATE TABLE " + PHOTO_LIST_TABLE + " ("
                + PHOTO_AUTO_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + PHOTO_COLUMN + " NTEXT,"
                + PHOTO_SETID + " INTEGER,"
                + PHOTO_CLIENTCOVER + " NTEXT,"
                + PHOTO_SETNAME + " NTEXT,"
                + PHOTO_IMGSUM + " NTEXT,"
                + PHOTO_DATE + " NTEXT"
                + ");");
        
        //图片列表
        db.execSQL("CREATE TABLE " + PIC_TABLE + " ("
                + PIC_AUTO_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + PIC_SETID + " NTEXT,"
                + PIC_CHANNEL + " NTEXT,"
                + PIC_DATE + " NTEXT,"
                + PIC_JSON + " NTEXT"
                + ");");
        
        // 已读状态列表
        db.execSQL("CREATE TABLE " + ARTICLE_READ_LIST_TABLE + " ("
                + ARTICLE_READ_AUTO_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + ARTICLE_READ_COL_ID + " NTEXT,"
                + ARTICLE_READ_DOC_ID + " NTEXT,"
                + ARTICLE_READ_DATE + " NTEXT,"
                + ARTICLE_READ_STATUS + " NTEXT"
                + ");");
        
        // 微博OAuth token
        db.execSQL("CREATE TABLE " + OAUTH_TABLE + " ("
                + OAUTH_AUTO_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + OAUTH_TYPE + " NTEXT,"
                + OAUTH_TOKEN + " NTEXT,"
                + OAUTH_TOKEN_SECRET + " NTEXT,"
                + OAUTH_NAME + " NTEXT"
                + ");");
        
     // 微博城市
        db.execSQL("CREATE TABLE " + CITY_TABLE + " ("
                + CITY_AUTO_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + CITY_NAME + " NTEXT,"
                + CITY_PROVINCE + " NTEXT,"
                + CITY_PINYIN + " NTEXT,"
                + CITY_SELECT_COUNT + " INTEGER"
                + ");");
        
     // 视频列表
        db.execSQL("CREATE TABLE " + VIDEO_LIST_TABLE + " ("
                + VIDEO_AUTO_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + VIDEO_COL_ID + " NTEXT,"
                + VIDEO_REPLY_COUNT + " NTEXT,"
                + VIDEO_DIGEST + " NTEXT,"
                + VIDEO_DIGEST_ALL + " NTEXT,"
                + VIDEO_TITLE + " NTEXT,"
                + VIDEO_HITS + " NTEXT,"
                + VIDEO_COVER + " NTEXT,"
                + VIDEO_REPLY_BOARD + " NTEXT,"
                + VIDEO_REPLY_ID + " NTEXT,"
                + VIDEO_MP4_URL + " NTEXT,"
                + VIDEO_LENGTH + " NTEXT,"
                + VIDEO_VURL + " NTEXT,"
                + VIDEO_VID + " NTEXT,"
                + VIDEO_PTIME +" NTEXT,"
                + VIDEO_EXTENDS_JSON +" NTEXT"
                + ");");
        
    
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
        Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS " + TOP_COLUMN_TABLE);
        db.execSQL("DROP TABLE IF EXISTS " + MORE_COLUMN_TABLE);
        db.execSQL("DROP TABLE IF EXISTS " + VOTE_TABLE);
        db.execSQL("DROP TABLE IF EXISTS " + COLLECT_TABLE);
        db.execSQL("DROP TABLE IF EXISTS " + NEWS_LIST_TABLE);
        db.execSQL("DROP TABLE IF EXISTS " + COMMENTS_LIST_TABLE);
        db.execSQL("DROP TABLE IF EXISTS " + PHOTO_LIST_TABLE);
        db.execSQL("DROP TABLE IF EXISTS " + PIC_TABLE);
        db.execSQL("DROP TABLE IF EXISTS " + ARTICLE_READ_LIST_TABLE);
        db.execSQL("DROP TABLE IF EXISTS " + OAUTH_TABLE);
        db.execSQL("DROP TABLE IF EXISTS " + CITY_TABLE);
        db.execSQL("DROP TABLE IF EXISTS " + VIDEO_LIST_TABLE);
        onCreate(db);
	}
	
	/**
	 * 数据库更新从2011-12-16 数据库版本为 73以后的版本开始执行 更新操作。
	 * 这个表不会删除以前的数据表，如果数据库有新的字段增加，那么会通过
	 * 增加字段来更新表
	 * @param db
	 */
	public void updateNewsDB(SQLiteDatabase db){
	    
	}

}
